﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL.Entity;

namespace DAL.Persistencia
{
    public class ServicoCompletoFileDAL
    {

        private NewTierraEntities Con;

        public ServicoCompletoFileDAL()
        {
            Con = new NewTierraEntities();
        }

        public void Salvar(Servico_Completo_File s)
        {
            try
            {
                Con.AddToServico_Completo_File(s);
                Con.SaveChanges();
            }
            catch 
            {                
                throw;
            }
        }

        public Servico_Completo_File ObterPorId(int IdServico)
        {
            try
            {
                return Con.Servico_Completo_File.Where(s => s.ServicoCompletoFile_id == IdServico).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public void Excluir(Servico_Completo_File s)
        {
            try
            {
                Con.DeleteObject(s);
                Con.SaveChanges();
            }
            catch
            {
                throw;
            }
        }

        public void Atualizar(Servico_Completo_File novo)
        {
            try
            {
                Servico_Completo_File antigo = ObterPorId(novo.ServicoCompletoFile_id);

                antigo.ServicoCompleto_de = novo.ServicoCompleto_de;
                antigo.ServicoCompleto_ate = novo.ServicoCompleto_ate;

                Con.SaveChanges();
            }
            catch
            {
                throw;
            }
        }

        public void AtualizarValor(Servico_Completo_File novo)
        {
            try
            {
                Servico_Completo_File antigo = ObterPorId(novo.ServicoCompletoFile_id);

                antigo.Valor = novo.Valor;               

                Con.SaveChanges();
            }
            catch
            {
                throw;
            }
        }

        public object ListarTodosSubServicoPorTransfer(int IdTransf, int BaseDe)
        {
            try
            {
                return (from ftr in Con.File_Transfers
                        join scf in Con.Servico_Completo_File on ftr.File_Transf_id equals scf.File_Transf_id
                        join mos in Con.Monta_Servico on scf.IdMServico equals mos.IdMServico
                        join sup in Con.Suppliers on mos.S_id equals sup.S_id
                        join msv in Con.Monta_Servico_Valores on mos.IdMServico equals msv.IdMServico
                        join sba in Con.S_Bases on msv.Base_id equals sba.Base_id
                        where scf.File_Transf_id == IdTransf &&
                              sba.Base_de <= BaseDe &&
                              sba.Base_ate >= BaseDe
                        select new
                        {
                            sup.S_nome,
                            sup.S_id,
                            scf.Valor,
                            ftr.Paxs,
                            scf.ServicoCompletoFile_id,
                            ItemSubServico_nome = mos.ItemSubServico.ItemSubServico_nome,
                            SubItem_nome = mos.SubItem.SubItem_nome                           

                        }).Distinct().ToList();

            }
            catch
            {
                throw;
            }
        }

        public List<Servico_Completo_File> ListarTodosPorTransf(int IdTransf)
        {
            try
            {
                return Con.Servico_Completo_File.Where(s => s.File_Transf_id == IdTransf).ToList();
            }
            catch 
            {                
                throw;
            }
        }

        public S_Bases ObterIdBase(int IdMServico, int BaseDe, int BaseIndex)
        {
            try
            {
                return (from mos in Con.Monta_Servico
                        join sup in Con.Suppliers on mos.S_id equals sup.S_id
                        join sba in Con.S_Bases on sup.S_id equals sba.S_id
                        where mos.IdMServico == IdMServico &&
                              sba.Base_de <= BaseDe &&
                              sba.Base_ate >= BaseDe &&
                              sba.Base_index == BaseIndex
                        select sba).Distinct().SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public S_Bases ObterIdBase(int IdMServico, int BaseDe)
        {
            try
            {
                return (from mos in Con.Monta_Servico
                        join sup in Con.Suppliers on mos.S_id equals sup.S_id
                        join sba in Con.S_Bases on sup.S_id equals sba.S_id
                        where mos.IdMServico == IdMServico &&
                              sba.Base_de <= BaseDe &&
                              sba.Base_ate >= BaseDe
                        select sba).Distinct().SingleOrDefault();
            }
            catch 
            {                
                throw;
            }
        }

        public Monta_Servico_Valores ObterValor(int IdMServico, int IdBase)
        {
            try
            {
                return Con.Monta_Servico_Valores.Where(m => m.Base_id == IdBase && m.IdMServico == IdMServico).SingleOrDefault();
            }
            catch 
            {                
                throw;
            }
        }

        public Monta_Servico_Valores ObterValor(int IdMServico, int PayPax, int BaseIndex)
        {
            try
            {

                return (from mos in Con.Monta_Servico
                        join msv in Con.Monta_Servico_Valores on mos.IdMServico equals msv.IdMServico
                        join sba in Con.S_Bases on msv.Base_id equals sba.Base_id                       
                        where mos.IdMServico == IdMServico &&
                              sba.Base_de <= PayPax &&
                              sba.Base_ate >= PayPax &&
                              sba.Base_index == BaseIndex
                        select msv).Distinct().SingleOrDefault();

            }
            catch 
            {                
                throw;
            }
        }

        public bool VerirficaExisteBase(int IdMServico, int PayPax, int BaseIndex)
        {
            try
            {

                return (from mos in Con.Monta_Servico
                        join msv in Con.Monta_Servico_Valores on mos.IdMServico equals msv.IdMServico
                        join sba in Con.S_Bases on msv.Base_id equals sba.Base_id
                        where mos.IdMServico == IdMServico &&
                              sba.Base_de <= PayPax &&
                              sba.Base_ate >= PayPax &&
                              sba.Base_index == BaseIndex
                        select msv).Count() != 0;

            }
            catch
            {
                throw;
            }
        }


    }
}
